import datetime
import socket
import pymysql
import threading


# 数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='s18772992132',
    database='transport',
    charset='utf8'
)
cursor = conn.cursor()

# UDP服务器设置
server = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
server.bind(('localhost', 12345))

# 存储所有客户端的地址
clients = []

# 存储已经处理过的 order_number 请求及其结果
processed_orders = {}
# 存储骑手ID到客户端地址的映射
rider_addr_mapping = {}  # {rider_id: (ip, port)}
# 存储商家ID到客户端地址的映射（可选）
merchant_addr_mapping = {}  # {merchant_id: (ip, port)}

def broadcast(message):
    to_remove = []
    for client in clients:
        try:
            print(client)
            server.sendto(message.encode(), client)
        except ConnectionResetError:
            print(f"客户端 {client} 已断开连接")
            to_remove.append(client)
    for client in to_remove:
        clients.remove(client)
def send_online_info():
    # 发送所有在线的站点（商家）和车辆（骑手）信息
    cursor.execute("SELECT station_id, 'merchant', x_coordinate, y_coordinate FROM stations")
    stations = cursor.fetchall()
    cursor.execute("SELECT vehicle_id, 'courier', current_x, current_y FROM vehicles WHERE status != 'offline'")
    vehicles = cursor.fetchall()
    info = []
    for station in stations:
        info.append(f"{station[0]},merchant,{station[2]},{station[3]}")
    for vehicle in vehicles:
        info.append(f"{vehicle[0]},courier,{vehicle[2]},{vehicle[3]}")
    message = 'ONLINE_INFO,' + ','.join(info)
    broadcast(message)

def handle_connection():
    while True:
        data, addr = server.recvfrom(1024)
        data = data.decode()
        if addr not in clients:
            clients.append(addr)

        if data.startswith("U"):
            _, user_id, user_role, password = data.split(',')
            cursor.execute("SELECT * FROM users WHERE user_id = %s AND user_role = %s AND password = %s", (user_id, user_role, password))
            result = cursor.fetchone()
            if result:
                username = result[1]
                server.sendto(f"H,{username}".encode(), addr)
                # 登录成功，更新地址映射
                if user_role == 'courier':
                    rider_addr_mapping[user_id] = addr
                    print(f"骑手 {user_id} 登录，地址: {addr}")
                elif user_role == 'merchant':
                    merchant_addr_mapping[user_id] = addr
                    print(f"商家 {user_id} 登录，地址: {addr}")
            else:
                server.sendto("M".encode(), addr)
        elif data.startswith("T"):
            parts = data.split(',')
            if len(parts) < 5:
                print(f"错误：注册消息格式错误，数据：{data}")
                continue
            user_id = parts[1]
            user_role = parts[2]
            username = parts[3]
            password = parts[4]
            clients.remove(addr)
            # 将用户基本信息插入 users 表
            cursor.execute("INSERT INTO users (user_id, user_role, username, password) VALUES (%s, %s, %s, %s)", (user_id, user_role, username, password))
            if user_role == 'courier':
                x = 0
                y = 0
                cursor.execute("INSERT INTO vehicles (current_x, current_y, status, max_capacity) VALUES (%s, %s, 'idle', %s)", (x, y, 100))  # 假设载重为100
                vehicle_id = cursor.lastrowid  # 获取最后插入的ID
                # 更新users表中的vehicle_id
                cursor.execute("UPDATE users SET vehicle_id = %s WHERE user_id = %s", (vehicle_id, user_id))
                rider_addr_mapping[user_id] = addr
                print(f"骑手 {user_id} 注册，记录地址: {addr}")
            elif user_role == 'merchant':
                x = 0
                y = 0
                # 插入站点信息到 stations 表
                cursor.execute("INSERT INTO stations (station_name, x_coordinate, y_coordinate, station_type) VALUES (%s, %s, %s, 'pickup')", (username, x, y))
                station_id = cursor.lastrowid  # 获取最后插入的ID
                cursor.execute("UPDATE users SET station_id = %s WHERE user_id = %s", (station_id, user_id))
                merchant_addr_mapping[user_id] = addr
                print(f"商家 {user_id} 注册，记录地址: {addr}")
            conn.commit()

        elif data.startswith("A"):
            # 处理A类型消息：广播新上线用户信息
            parts = data.split(',')
            if len(parts) < 5:
                print(f"错误：A类型消息格式错误，数据：{data}")
                continue
            user_id = parts[1]
            user_role = parts[2]
            x = parts[3]
            y = parts[4]
            max_capacity = parts[5] if len(parts) > 5 else None
            
            # 构建广播消息
            broadcast(data)            
            print(f"已广播新上线用户信息：{data}")
        elif data.startswith("GET_ONLINE_INFO"):
            # 发送所有在线的站点（商家）和车辆（骑手）信息
            send_online_info()
        elif data.startswith("D"):
            cursor.execute("delete from orders ")
            # 正确解析：拆分后应为 [前缀, order_number, pickup_station_id, delivery_x, delivery_y, weight]
            parts = data.split(',')
            if len(parts) != 8:  # 格式：CREATE_ORDER,ORDER_20250703xxxx,1,11.000000,22.000000,50.00
                print(f"错误：参数数量不对，需 8 个（当前 {len(parts)} 个），数据：{data}")
                continue

            _, order_number, pickup_station_id, _,_,delivery_x, delivery_y, weight = parts
            broadcast(data);
               
        elif data.startswith("ok"):
            # 处理OK消息：转发订单请求给骑手
            parts = data.split(',')
            if len(parts) < 3:  # OK,订单ID,商家ID
                print(f"错误：OK消息格式错误，数据：{data}")
                continue
            order_id = parts[1]
            merchant_id = parts[2]
            
            # 查找订单信息
            try:
                broadcast(data)
                # 更新订单状态为"processing"
                cursor.execute("UPDATE orders SET order_status = 'processing' WHERE order_number = %s", (order_id))
                conn.commit()
                print(f"订单 {order_id} 状态已更新为 processing")
            except Exception as e:
                print(f"处理OK消息失败：{e}")
                conn.rollback()
        elif data.startswith("finish"):
            # 处理FINISH消息，动态计算耗时
            parts = data.split(',')
            if len(parts) < 2:  # FINISH,订单ID
                print(f"错误：FINISH消息格式错误，数据：{data}")
                continue
            order_id = parts[1]
            print(order_id)
            cursor.execute("delete from orders where order_number = %s", (order_id))
            broadcast(data);
        elif data.startswith("C"):
            # 处理C消息：广播用户坐标给所有用户
            parts = data.split(',')
            if len(parts) != 5:  # C,用户ID,身份,坐标x,坐标y
                print(f"错误：C消息格式错误，数据：{data}")
                continue
            user_id = parts[1]
            user_role = parts[2]
            x = parts[3]
            y = parts[4]
    
            try:
                # 根据身份更新对应地址映射中的坐标（用于记录最新坐标）
                if user_role == "merchant":
                    # 商家坐标关联station_id
                    cursor.execute("SELECT station_id FROM users WHERE user_id = %s", (user_id,))
                    merchant_station = cursor.fetchone()
                    if merchant_station:
                        cursor.execute(
                            "UPDATE stations SET x_coordinate = %s, y_coordinate = %s WHERE station_id = %s",
                            (x, y, merchant_station[0])
                        )
                        conn.commit()
                        print(f"商家 {user_id} 坐标更新为 ({x}, {y})")
                elif user_role == "courier":
                    # 更新骑手车辆坐标（vehicle_id与user_id关联）
                    cursor.execute("SELECT vehicle_id FROM users WHERE user_id = %s", (user_id,))
                    rider_vehicle = cursor.fetchone()
                    if rider_vehicle:
                        cursor.execute(
                            "UPDATE vehicles SET current_x = %s, current_y = %s WHERE vehicle_id = %s",
                            (x, y, rider_vehicle[0])
                        )
                        conn.commit()
                        print(f"骑手 {user_id} 坐标更新为 ({x}, {y})")
        
                # 广播坐标消息给所有客户端
                broadcast(data)
                print(f"已广播用户 {user_id} 的坐标信息")
            except Exception as e:
                print(f"处理C消息失败：{e}")
                conn.rollback()
        elif data.startswith("F"):
            # 处理F消息：发送订单请求给骑手
            parts = data.split(',')   
            broadcast(data);    
            # 更新订单状态为"processing"
            #cursor.execute("UPDATE orders SET order_status = 'processing' WHERE order_id = %s", (order_id,))
            #conn.commit()
            #print(f"订单 {order_id} 状态已更新为 processing")
        elif data.startswith('Q'):
            broadcast(data)
            clients.remove(addr)
            
        
           

if __name__ == "__main__":
    print("Server started...")
    thread = threading.Thread(target=handle_connection)
    thread.start()

    # 可以在这里添加定时发送在线信息的逻辑，例如每隔一段时间发送一次
    # 以下是一个简单的示例，每隔60秒发送一次在线信息
    #import time
    #while True:
    #    send_online_info()
    #    time.sleep(60)